
/*
'===============================================================================
'  HNG.Portal.BL.TblMenuManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using HNG.Portal.BLL;
using HNG.Portal.DAL;
using HNG.Portal.DAL.EntityClasses;
using HNG.Portal.DAL.FactoryClasses;
using HNG.Portal.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using System.Text;
namespace HNG.Portal.BLL
{
	public class TblMenuManager : TblMenuManagerBase
	{
        SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
		/// <summary>
		/// Purpose: Class constructor.
		/// </summary>
		public TblMenuManager()
		{
			// Nothing for now.
		}
        
        public DataTable be_selectToGridView(TblMenuEntity _TblMenuEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[4];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblMenuEntity.SortExpression) ? "[ID]" : _TblMenuEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblMenuEntity.SortDirection) ? " DESC" : _TblMenuEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[TieuDe],").Append("\n");
                sbSql.Append("  ch.[Url],").Append("\n");
                sbSql.Append("  ch.[Target],").Append("\n");
                sbSql.Append("  ch.[ThuTu],").Append("\n");
                sbSql.Append("  ch.[TenDanhMucCha],").Append("\n");
                sbSql.Append("  ch.[TrangThai]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblMenu ch").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblMenuEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@Id", _TblMenuEntity.Id);
                    sbSql.Append("  AND ch.[ID] LIKE @Id").Append("\n");

                }

                string strCon = _TblMenuEntity.Condition;
                if (!string.IsNullOrEmpty(strCon))
                {
                    sbSql.AppendFormat("  AND (ch.[TieuDe] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Url] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Target] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[MoTa] LIKE '%{0}%'", strCon).Append("\n");

                    sbSql.AppendFormat("  OR ch.[TieuDe] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Url] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Target] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[MoTa] LIKE N'%{0}%'", strCon).Append("\n");

                    sbSql.Append(")").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblMenuEntity.LangId);
                    sbSql.Append("  AND ch.[LangId] LIKE @LangId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.ViTri))
                {
                    _SqlParameter[2] = new SqlParameter("@ViTri", _TblMenuEntity.ViTri);
                    sbSql.Append("  AND ch.[ViTri] LIKE @ViTri").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.ShowType))
                {
                    _SqlParameter[3] = new SqlParameter("@ShowType", _TblMenuEntity.ShowType);
                    sbSql.Append("  AND ch.[ShowType] LIKE @ShowType").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.DM))
                {
                    _SqlParameter[4] = new SqlParameter("@DanhMucID", _TblMenuEntity.DanhMucId);
                    sbSql.Append("  AND ch.[DanhMucID] LIKE @DanhMucID").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.strParentID))
                {
                    _SqlParameter[4] = new SqlParameter("@ParentId", _TblMenuEntity.ParentId);
                    sbSql.Append("  AND ch.[ParentId] LIKE @ParentId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.Status))
                {
                    _SqlParameter[4] = new SqlParameter("@TrangThai", _TblMenuEntity.TrangThai);
                    sbSql.Append("  AND ch.[TrangThai] LIKE @TrangThai").Append("\n");
                }

                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblMenuEntity.StartIndex, _TblMenuEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public DataTable be_SelectToDropdownlist(TblMenuEntity _TblMenuEntity)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            StringBuilder sbSql = new StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[2];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblMenuEntity.SortExpression) ? "[ID]" : _TblMenuEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblMenuEntity.SortDirection) ? " DESC" : _TblMenuEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[TieuDe],").Append("\n");
                sbSql.Append("  ch.[ParentID]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblMenu ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblLang lng").Append("\n");
                sbSql.Append("  ON lng.ID = ch.LangID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblMenuEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@ID", _TblMenuEntity.Id);
                    sbSql.Append("  AND ch.[ID] LIKE @ID").Append("\n");
                }
                if (!string.IsNullOrEmpty(_TblMenuEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblMenuEntity.LangId);
                    sbSql.Append("  AND ch.[LangID] Like @LangId").Append("\n");
                }

                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblMenuEntity.StartIndex, _TblMenuEntity.EndIndex).Append("\n");
            }
            catch { }


            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public int be_UpdateOrderUp(int CateID)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            _SqlParameter[0] = new SqlParameter("@ID", CateID);
            return AE.SqlHelper.ExecuteNonQuery(Conn, "sp_MenuOrdersUp", _SqlParameter);
        }

        public int be_UpdateOrderDown(int CateID)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            _SqlParameter[0] = new SqlParameter("@ID", CateID);
            return AE.SqlHelper.ExecuteNonQuery(Conn, "sp_MenuOrdersDown", _SqlParameter);
        }

        public DataTable fe_selectMenu(TblMenuEntity _TblMenuEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[8];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblMenuEntity.SortExpression) ? "[ID]" : _TblMenuEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblMenuEntity.SortDirection) ? " DESC" : _TblMenuEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[TieuDe],").Append("\n");
                sbSql.Append("  ch.[Url],").Append("\n");
                sbSql.Append("  ch.[Target],").Append("\n");
                sbSql.Append("  ch.[TenDanhMucCha],").Append("\n");
                sbSql.Append("  ch.[ParentID],").Append("\n");
                sbSql.Append("  ch.[ShowType],").Append("\n");
                sbSql.Append("  ch.[DanhMucID],").Append("\n");
                sbSql.Append("  ch.[DanhMucDetailID],").Append("\n");
                sbSql.Append("  ch.[ViTri],").Append("\n");
                sbSql.Append("  ch.[ThuTu],").Append("\n");
                sbSql.Append("  ch.[TrangThai]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblMenu ch").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblMenuEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@Id", _TblMenuEntity.Id);
                    sbSql.Append("  AND ch.[ID] LIKE @Id").Append("\n");

                }

                string strCon = _TblMenuEntity.Condition;
                if (!string.IsNullOrEmpty(strCon))
                {
                    sbSql.AppendFormat("  AND (ch.[TieuDe] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Url] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Target] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[MoTa] LIKE '%{0}%'", strCon).Append("\n");

                    sbSql.AppendFormat("  OR ch.[TieuDe] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Url] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Target] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[MoTa] LIKE N'%{0}%'", strCon).Append("\n");

                    sbSql.Append(")").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblMenuEntity.LangId);
                    sbSql.Append("  AND ch.[LangId] LIKE @LangId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.ViTri))
                {
                    _SqlParameter[2] = new SqlParameter("@ViTri", _TblMenuEntity.ViTri);
                    sbSql.Append("  AND (ch.[ViTri] LIKE @ViTri OR ch.[ViTri] LIKE 'All')").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.ShowType))
                {
                    _SqlParameter[3] = new SqlParameter("@ShowType", _TblMenuEntity.ShowType);
                    sbSql.Append("  AND ch.[ShowType] LIKE @ShowType").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.DM))
                {
                    _SqlParameter[4] = new SqlParameter("@DanhMucID", _TblMenuEntity.DanhMucId);
                    sbSql.Append("  AND ch.[DanhMucID] LIKE @DanhMucID").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.strParentID))
                {
                    _SqlParameter[5] = new SqlParameter("@ParentId", _TblMenuEntity.strParentID);
                    sbSql.Append("  AND ch.[ParentId] LIKE @ParentId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.strDMdetail))
                {
                    _SqlParameter[6] = new SqlParameter("@strDMdetail", _TblMenuEntity.strDMdetail);
                    sbSql.Append("  AND ch.[DanhMucDetailID] LIKE @strDMdetail").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.Status))
                {
                    _SqlParameter[7] = new SqlParameter("@TrangThai", _TblMenuEntity.TrangThai);
                    sbSql.Append("  AND ch.[TrangThai] LIKE @TrangThai").Append("\n");
                }

                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblMenuEntity.StartIndex, _TblMenuEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }
	}
}
